﻿using System.Data.SqlClient;
using System.Text.Json;
using Application.StockService;
using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.search;
using Domain.IRepository;
using EFCore.Repository;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Distributed;

namespace WMSManage.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]//需要鉴权
    public class StockController : ControllerBase
    {
        private readonly IStockService stockService;
        private readonly IDistributedCache distributedCache;
        public StockController(IStockService stockService, IDistributedCache distributedCache)
        {
            this.stockService = stockService;
            this.distributedCache = distributedCache;

        }

        /// <summary>
        /// 查询出入库汇总信息
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<StockTotals> Gettotal()
        {
            return await stockService.Gettotal();
        }

        /// <summary>
        /// 添加库存
        /// </summary>
        /// <param name="addStockDto"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<int> AddStock(AddStockDto addStockDto)
        {
            return await stockService.AddStock(addStockDto);
        }


        /// <summary>
        /// 库存查询
        /// </summary>
        /// <param name="showStopped">显示停用商品</param>
        /// <param name="filterZeroStock">过滤零库存</param>
        /// <param name="name">货品名称/编码(模糊查询)</param>
        /// <param name="unit">单位</param>
        /// <param name="whid">仓库ID</param>
        /// <param name="type">货品类型</param>
        /// <param name="startTime">创建时间开始范围</param>
        /// <param name="endTime">创建时间结束范围</param>
        /// <param name="currentPage">当前页码</param>
        /// <param name="pageSize">每页条数(默认10)</param>
        /// <returns>库存分页数据</returns>
        [HttpGet]
        public async Task<IActionResult> GetStockAsync(
            string? startTime,
            string? endTime,
            [FromQuery] string? name,
            [FromQuery] string? unit,
            [FromQuery] int? whid,
            [FromQuery] string? type,
            [FromQuery] int currentPage = 1, 
            [FromQuery] int pageSize = 10,
            bool showStopped = false,
            bool filterZeroStock = false)
        {
            var pagination = new FenYe<StockInventoryViewDto>
            {
                CurrentPage = currentPage,
                PageSize = pageSize
            };

            try
            {
                var result = await stockService.GetStockAsync(pagination, name, unit, whid, type, showStopped,filterZeroStock);
                return Ok(result);
            }
            catch (Exception ex)
            {
                return StatusCode(500, "查询库存时发生错误");
            }
        }

        /// <summary>
        /// 库存分布
        /// </summary>
        /// <param name="timeType">时间</param>
        /// <param name="page">当前页码</param>
        /// <param name="pagesize">每页条数(默认10)</param>
        /// <returns>库存分页数据</returns>
        [HttpGet]
        public async Task<StockTJResult> StockDistribution(string? timeType, int? page, int? pagesize)
        {
            // 缓存键
            string key = $"StockDistribution_{timeType}_{page}_{pagesize}";

            // 尝试从缓存获取
            var cachedData = await distributedCache.GetStringAsync(key);
            if (cachedData != null)
            {
                return JsonSerializer.Deserialize<StockTJResult>(cachedData);
            }

            // 从数据库查询
            var result = await stockService.StockDto_hTJ(timeType, page, pagesize);

            // 实现分页（如果需要在服务端分页）
            if (page.HasValue && pagesize.HasValue)
            {
                int skip = (page.Value - 1) * pagesize.Value;
                result.list = result.list.Skip(skip).Take(pagesize.Value).ToList();
            }

            // 缓存结果
            var options = new DistributedCacheEntryOptions
            {
                AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10)
            };
            await distributedCache.SetStringAsync(key, JsonSerializer.Serialize(result), options);

            return result;
        }


        /// <summary>
        /// 查询是否已存在相同商品、仓库、库位的记录
        /// </summary>
        /// <param name="procid"></param>
        /// <param name="whid"></param>
        /// <param name="slotid"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<Stock> GetStockByCriteria(int procid, int whid, int slotid)
        {
            return await stockService.GetStockByCriteria(procid, whid, slotid);
        }

        /// <summary>
        /// 查询库存
        /// </summary>
        /// <param name="whid"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<List<Stock>> GetStockwhid(int whid)
        {
            return await stockService.GetStockwhid(whid);
        }




        /// <summary>
        /// 保质期查询
        /// </summary>
        /// <param name="productName"></param>
        /// <param name="productType"></param>
        /// <param name="warehouseId"></param>
        /// <param name="unit"></param>
        /// <param name="status"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetShelfLifeQuery(string? productName, int? productType, int? warehouseId, string? unit, int currentPage, int pageSize = 10)
        {
            try
            {
                string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";
                using (var connection = new SqlConnection(con))
                {
                    var parameters = new DynamicParameters();
                    var where = " p.is_datamanagement=1 ";

                    if (!string.IsNullOrEmpty(productName))
                    {
                        where += " AND (p.proc_name LIKE @productName OR p.proc_code LIKE @productName)";
                        parameters.Add("@productName", $"%{productName}%");
                    }

                    if (productType != 0 && productType != null)
                    {
                        where += " AND p.type_id = @productType";
                        parameters.Add("@productType", productType); // 移除了多余的%符号
                    }

                    if (warehouseId.HasValue && warehouseId.Value > 0)
                    {
                        where += " AND w.wh_id = @warehouseId";
                        parameters.Add("@warehouseId", warehouseId.Value);
                    }

                    if (!string.IsNullOrEmpty(unit))
                    {
                        where += " AND p.proc_primary_unit = @unit";
                        parameters.Add("@unit", unit);
                    }

                    // 1. 先查询总记录数
                    var countSql = @$"
                    SELECT DISTINCT s.*, p.proc_id, p.proc_code, p.proc_name, p.type_id, p.type_name, 
                            p.proc_model, p.proc_primary_unit, p.proc_primary_barcode,
                            w.wh_id, w.wh_name, t.slot_name, s.stk_hand_num, 
                            d.proc_date, p.proc_expirationdate
                    FROM [dbo].[bs_stock] s
                    LEFT JOIN (
                        SELECT proc_id, proc_date, 
                               ROW_NUMBER() OVER (PARTITION BY proc_id ORDER BY proc_date DESC) as rn
                        FROM [dbo].[in_instock_d]
                    ) d ON d.proc_id = s.proc_id AND d.rn = 1
                    LEFT JOIN [dbo].[bs_product] p ON p.proc_id = s.proc_id
                    LEFT JOIN [dbo].[bs_warehouse] w ON w.wh_id = s.wh_id
                    LEFT JOIN [dbo].[bs_slot] t ON t.slot_id = s.slot_id
                    where {where}";

                    int totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                    // 2. 查询分页数据
                    var dataSql = @$"
                    SELECT DISTINCT s.*, p.proc_id, p.proc_code, p.proc_name, p.type_id, p.type_name, 
                            p.proc_model, p.proc_primary_unit, p.proc_primary_barcode,
                            w.wh_id, w.wh_name, t.slot_name, s.stk_hand_num, 
                            d.proc_date, p.proc_expirationdate
                    FROM [dbo].[bs_stock] s
                    LEFT JOIN (
                        SELECT proc_id, proc_date, 
                               ROW_NUMBER() OVER (PARTITION BY proc_id ORDER BY proc_date DESC) as rn
                        FROM [dbo].[in_instock_d]
                    ) d ON d.proc_id = s.proc_id AND d.rn = 1
                    LEFT JOIN [dbo].[bs_product] p ON p.proc_id = s.proc_id
                    LEFT JOIN [dbo].[bs_warehouse] w ON w.wh_id = s.wh_id
                    LEFT JOIN [dbo].[bs_slot] t ON t.slot_id = s.slot_id
                    where {where}
                    ORDER BY p.proc_id, proc_code
                    OFFSET @Offset ROWS
                    FETCH NEXT @PageSize ROWS ONLY;";

                    // 添加分页参数
                    parameters.Add("@Offset", (currentPage - 1) * pageSize);
                    parameters.Add("@PageSize", pageSize);

                    var data = await connection.QueryAsync(dataSql, parameters);

                    // 3. 返回分页结果
                    return Ok(new
                    {
                        Success = true,
                        Data = data,
                        TotalCount = totalCount,
                        CurrentPage = currentPage,
                        PageSize = pageSize,
                        TotalPages = (int)Math.Ceiling(totalCount / (double)pageSize)
                    });
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new { Success = false, Message = ex.Message });
            }
        }




        /// <summary>
        /// 库存分布
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetStockDistribution(int page = 1, int pagesize = 10)
        {
            try
            {
                string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";
                using (var connection = new SqlConnection(con))
                {
                    // 按仓库分组统计库存数据，并关联仓库表获取真实仓库名称
                    var sql = $@"
                    WITH StockSummary AS (
                        SELECT 
                            s.wh_id,
                            w.wh_name,
                            SUM(s.stk_hand_num) as total_quantity,
                            SUM(s.stk_total) as total_amount
                        FROM bs_stock s
                        INNER JOIN bs_warehouse w ON s.wh_id = w.wh_id
                        WHERE s.stk_hand_num > 0 AND w.is_delete = 0
                        GROUP BY s.wh_id, w.wh_name
                    ),
                    PagedData AS (
                        SELECT *,
                               ROW_NUMBER() OVER (ORDER BY total_amount DESC) as RowNum
                        FROM StockSummary
                    )
                    SELECT 
                        wh_id,
                        wh_name,
                        total_quantity as stk_hand_num,
                        total_amount as stk_total
                    FROM PagedData
                    WHERE RowNum BETWEEN {(page - 1) * pagesize + 1} AND {page * pagesize}
                    ORDER BY total_amount DESC";

                    var data = await connection.QueryAsync(sql);

                    // 查询总计数据
                    var summarySql = @"
                    SELECT 
                        COUNT(DISTINCT s.wh_id) as total_warehouses,
                        SUM(s.stk_hand_num) as total_quantity,
                        SUM(s.stk_total) as total_amount
                    FROM bs_stock s
                    INNER JOIN bs_warehouse w ON s.wh_id = w.wh_id
                    WHERE s.stk_hand_num > 0 AND w.is_delete = 0";

                    var summary = await connection.QueryFirstOrDefaultAsync(summarySql);

                    // 为分页添加序号
                    var list = data.Select((item, index) => new
                    {
                        index = (page - 1) * pagesize + index + 1,
                        wh_id = item.wh_id,
                        wh_name = item.wh_name,  // 使用真实的仓库名称
                        stk_hand_num = Convert.ToInt32(item.stk_hand_num),
                        stk_total = Convert.ToDecimal(item.stk_total)
                    }).ToList();

                    return Ok(new
                    {
                        success = true,
                        list = list,
                        total = summary?.total_warehouses ?? 0,
                        total_stk_hand_num = summary?.total_quantity ?? 0,
                        total_stk_total = summary?.total_amount ?? 0
                    });
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new { success = false, message = ex.Message });
            }
        }


        /// <summary>
        /// 待入待出库统计
        /// </summary>
     


        /// <summary>
        /// 仓库汇总
        /// </summary>
        /// <param name="timeType"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetWarehouseRollup(string timeType = "today", int pageIndex = 1, int pageSize = 10)
        {
            try
            {
                string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";
                using (var connection = new SqlConnection(con))
                {
                    // 根据时间类型确定日期范围
                    var (startDate, endDate) = GetDateRange(timeType);

                    // 计算总数的SQL
                    var countSql = @"
                    SELECT COUNT(*)
                    FROM [dbo].[bs_warehouse] w
                    WHERE w.is_delete = 0";

                        // 分页查询的SQL
                        var sql = @"
                    SELECT 
                        w.wh_id,
                        w.wh_name as warehouseName,
                        ISNULL((SELECT COUNT(DISTINCT proc_id) FROM [dbo].[bs_stock] WHERE wh_id = w.wh_id AND stk_hand_num > 0), 0) as productType,
                        ISNULL((SELECT SUM(stk_total) FROM [dbo].[bs_stock] WHERE wh_id = w.wh_id), 0) as totalAmount,
    
                        -- 入库统计
                        ISNULL((SELECT COUNT(DISTINCT ih.in_id) 
                                FROM [dbo].[in_instock_h] ih 
                                WHERE ih.wh_id = w.wh_id 
                                AND CAST(ih.in_date AS DATE) >= CAST(@startDate AS DATE) 
                                AND CAST(ih.in_date AS DATE) <= CAST(@endDate AS DATE)
                                AND ih.in_status = 6), 0) as inboundCount,
            
                        ISNULL((SELECT SUM(id.ind_num) 
                                FROM [dbo].[in_instock_h] ih 
                                INNER JOIN [dbo].[in_instock_d] id ON ih.in_id = id.in_id
                                WHERE ih.wh_id = w.wh_id 
                                AND CAST(ih.in_date AS DATE) >= CAST(@startDate AS DATE) 
                                AND CAST(ih.in_date AS DATE) <= CAST(@endDate AS DATE)
                                AND ih.in_status = 6), 0) as inboundQuantity,
            
                        ISNULL((SELECT SUM(id.ind_total) 
                                FROM [dbo].[in_instock_h] ih 
                                INNER JOIN [dbo].[in_instock_d] id ON ih.in_id = id.in_id
                                WHERE ih.wh_id = w.wh_id 
                                AND CAST(ih.in_date AS DATE) >= CAST(@startDate AS DATE) 
                                AND CAST(ih.in_date AS DATE) <= CAST(@endDate AS DATE)
                                AND ih.in_status = 6), 0) as inboundAmount,
    
                        -- 出库统计
                        ISNULL((SELECT COUNT(DISTINCT oh.out_id) 
                                FROM [dbo].[out_outstock_h] oh 
                                WHERE oh.wh_id = w.wh_id 
                                AND CAST(oh.out_date AS DATE) >= CAST(@startDate AS DATE) 
                                AND CAST(oh.out_date AS DATE) <= CAST(@endDate AS DATE)
                                AND oh.out_status = 6), 0) as outboundCount,
            
                        ISNULL((SELECT SUM(od.outd_num) 
                                FROM [dbo].[out_outstock_h] oh 
                                INNER JOIN [dbo].[out_outstock_d] od ON oh.out_id = od.out_id
                                WHERE oh.wh_id = w.wh_id 
                                AND CAST(oh.out_date AS DATE) >= CAST(@startDate AS DATE) 
                                AND CAST(oh.out_date AS DATE) <= CAST(@endDate AS DATE)
                                AND oh.out_status = 6), 0) as outboundQuantity,
            
                        ISNULL((SELECT SUM(od.outd_total) 
                                FROM [dbo].[out_outstock_h] oh 
                                INNER JOIN [dbo].[out_outstock_d] od ON oh.out_id = od.out_id
                                WHERE oh.wh_id = w.wh_id 
                                AND CAST(oh.out_date AS DATE) >= CAST(@startDate AS DATE) 
                                AND CAST(oh.out_date AS DATE) <= CAST(@endDate AS DATE)
                                AND oh.out_status = 6), 0) as outboundAmount
    
                        FROM [dbo].[bs_warehouse] w
                        WHERE w.is_delete = 0
                        ORDER BY w.wh_id
                        OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY";

                    var parameters = new
                    {
                        startDate,
                        endDate,
                        offset = (pageIndex - 1) * pageSize,
                        pageSize
                    };

                    // 获取总数
                    var totalCount = await connection.QuerySingleAsync<int>(countSql);

                    // 获取分页数据
                    var data = await connection.QueryAsync(sql, parameters);

                    return Ok(new
                    {
                        success = true,
                        data = data.ToList(),
                        total = totalCount,
                        pageIndex = pageIndex,
                        pageSize = pageSize
                    });
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new
                {
                    success = false,
                    message = ex.Message
                });
            }
        }

        private (string startDate, string endDate) GetDateRange(string timeType)
        {
            var now = DateTime.Now;
            var startDate = "";
            var endDate = "";

            switch (timeType)
            {
                case "today":
                    startDate = now.ToString("yyyy-MM-dd 00:00:00");
                    endDate = now.ToString("yyyy-MM-dd 23:59:59");
                    break;
                case "yesterday":
                    var yesterday = now.AddDays(-1);
                    startDate = yesterday.ToString("yyyy-MM-dd 00:00:00");
                    endDate = yesterday.ToString("yyyy-MM-dd 23:59:59");
                    break;
                case "week":
                    var weekStart = now.AddDays(-(int)now.DayOfWeek + 1);
                    startDate = weekStart.ToString("yyyy-MM-dd 00:00:00");
                    endDate = now.ToString("yyyy-MM-dd 23:59:59");
                    break;
                case "lastWeek":
                    var lastWeekStart = now.AddDays(-(int)now.DayOfWeek - 6);
                    var lastWeekEnd = now.AddDays(-(int)now.DayOfWeek);
                    startDate = lastWeekStart.ToString("yyyy-MM-dd 00:00:00");
                    endDate = lastWeekEnd.ToString("yyyy-MM-dd 23:59:59");
                    break;
                case "month":
                    startDate = new DateTime(now.Year, now.Month, 1).ToString("yyyy-MM-dd 00:00:00");
                    endDate = now.ToString("yyyy-MM-dd 23:59:59");
                    break;
                case "lastMonth":
                    var lastMonth = now.AddMonths(-1);
                    startDate = new DateTime(lastMonth.Year, lastMonth.Month, 1).ToString("yyyy-MM-dd 00:00:00");
                    endDate = new DateTime(lastMonth.Year, lastMonth.Month, DateTime.DaysInMonth(lastMonth.Year, lastMonth.Month)).ToString("yyyy-MM-dd 23:59:59");
                    break;
                default:
                    startDate = now.ToString("yyyy-MM-dd 00:00:00");
                    endDate = now.ToString("yyyy-MM-dd 23:59:59");
                    break;
            }

            return (startDate, endDate);
        }




        /// <summary>
        /// 查询货品库存
        /// </summary>
        /// <param name="procid"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<List<StockSearch>> GetByPid(int procid)
        {
            return await stockService.GetByPid(procid);
        }
    }
}
